/*
 * @(#)GSPayReceiveProcessor.java 
 *
 * Project: BUS
 *
 * Modify Information:
 * =============================================================================
 *   Author       Date       Description
 *   simon                   建立
 *   Jenny Li     2006-5-16  添加方法getUnCompletedPayRecvInfo，updatePayRecvInfo，addPayRecvInfo
 *   Jenny Li     2006-6-9   添加方法getCurMonthPayRecvInfo
 * Copyright Notice:
 * =============================================================================
 *    Copyright (c) 2001-2005 Beijing HiTRUST Technology Co., Ltd. 1808 Room, 
 *        Science & Technology Building, No. 9 South Street, Zhong Guan Cun, 
 *        Haidian District, Beijing ,100081, China All rights reserved.
 *
 *    This software is the confidential and proprietary information of
 *        Beijing HiTRUST Technology Co., Ltd. ("Confidential Information").
 *        You shall not disclose such Confidential Information and shall use it
 *        only in accordance with the terms of the license agreement you entered
 *        into with HiTRUST.
 *
 * Warning:
 * =============================================================================
 * 
 */
package com.hitrust.gs.business;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import com.hitrust.gs.bean.GSOperatorBean;
import com.hitrust.gs.bean.GSPayReceiveAbleBean;
import com.hitrust.gs.bean.GSPayReceiveMentBean;
import com.hitrust.gs.bean.GSProviderBean;
import com.hitrust.gs.dao.GSBalanceFeeDAO;
import com.hitrust.gs.dao.GSManufactureDAO;
import com.hitrust.gs.dao.GSPayReceiveAbleDAO;
import com.hitrust.gs.dao.GSProviderDAO;
import com.hitrust.gs.lib.GSBaseConstants;
import com.hitrust.gs.lib.GSLogWriter;
import com.hitrust.gs.lib.GSSystemConfigurator;
import com.hitrust.gs.service.GSBaseProcesser;
import com.hitrust.gs.util.GenRandNum;
import com.hitrust.gs.util.date.DateUtil;
import com.hitrust.gs.util.exception.GSException;
import com.hitrust.gs.util.lang.StringUtils;
import com.hitrust.gs.vo.GSBalPayRecvAddVO;
import com.hitrust.gs.vo.GSBalPayRecvManageQryResultVO;
import com.hitrust.gs.vo.GSBalPayRecvManageVO;
import com.hitrust.gs.vo.GSBalanceManageQueryVO;
import com.hitrust.gs.vo.GSPayReceiveAbleStatusListVO;
import com.hitrust.gs.vo.GSPayReceiveAlertListVO;
import com.hitrust.gs.vo.GSPayReceiveAlertQueryVO;
import com.hitrust.gs.vo.GSPayReceiveMentStatusListVO;
import com.hitrust.gs.vo.GSPayReceiveQueryVO;
import com.hitrust.gs.vo.GSPayReceiveSaveVO;
import com.hitrust.gs.vo.GSPayReceiveStatusQueryVO;


/**
 * @author simonyi
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class GSPayReceiveProcessor implements GSBaseProcesser  {
    
    private String FEE_PRE_RECEIVE = "990";//预收款
    private String FEE_PRE_PAYMENT = "991";//预付款

    String tPayReceiveAbleSql = "insert into GS_PAY_RECEIVE_ABLE (GS_WORKCORP_ID,GS_WORKCORP_CATEGORY," +
			"GS_BALANCE_MONTH,GS_NOTES,GS_WORKCORP_LEVEL,GS_WORKCORP_ID_GROUP,GS_FEE_TYPE_ID," +
			"GS_PAY_DATE,GS_PAY_TYPE,GS_BALANCE_NEED,GS_BALANCE_DONE,GS_BALANCE_UNDONE,GS_OFFSET_DATE," +
			"GS_PAY_STATUS,GS_PRINCIPAL,GS_OPERATOR_ID,GS_OPERATE_DATE,GS_INVOICE_CODE,GS_RECEIVEABLE_ID) " +
			"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";    

    public ArrayList getPayReceiveAble(GSOperatorBean operatorBean,GSPayReceiveQueryVO vo) throws GSException {
        ArrayList ary = null;
        StringBuffer sqlBuff = new StringBuffer("");
        
        if("1".equals(vo.deptcategory)){
            sqlBuff.append("select T1.gs_receiveable_id as P0,T1.gs_workcorp_id as P1,T1.gs_org_name as P2,T1.GS_PAY_TYPE as P3,T1.GS_FEE_TYPE_ID as P4,");
            sqlBuff.append("T1.GS_BALANCE_UNDONE as P5,T1.GS_PAY_DATE as P6,T1.GS_PRINCIPAL as P7,T1.GS_BALANCE_MONTH as P8, ");
            sqlBuff.append("T2.GS_FEE_TYPE as P9,T1.GS_OFFSET_DATE as P10 from ");
            sqlBuff.append("(select a.gs_receiveable_id ,a.gs_workcorp_id ,b.gs_org_name ,GS_PAY_TYPE ,a.GS_FEE_TYPE_ID ,");
            sqlBuff.append("a.GS_BALANCE_UNDONE ,a.GS_PAY_DATE ,GS_PRINCIPAL ,GS_BALANCE_MONTH ,GS_OFFSET_DATE ");
            sqlBuff.append("from GS_PAY_RECEIVE_ABLE a,GS_ORGANIZATION b ");
            sqlBuff.append("where a.gs_fee_type_id <> '990' and a.gs_fee_type_id <>'991' and a.GS_WORKCORP_ID=b.gs_org_id and GS_PAY_STATUS<>'3' and b.gs_org_id = '"+vo.deptId+"' ");
            sqlBuff.append(") T1 LEFT JOIN GS_balance_fee T2 ON T1.GS_FEE_TYPE_ID = T2.GS_fee_type_no ");
            sqlBuff.append("order by GS_PAY_DATE ASC, GS_OFFSET_DATE ASC");
        }
        else if("2".equals(vo.deptcategory)){
            sqlBuff.append("select T1.gs_receiveable_id as P0,T1.gs_workcorp_id as P1,T1.gs_provider_name as P2,T1.GS_PAY_TYPE as P3,T1.GS_FEE_TYPE_ID as P4,");
            sqlBuff.append("T1.GS_BALANCE_UNDONE as P5,T1.GS_PAY_DATE as P6,T1.GS_PRINCIPAL as P7,T1.GS_BALANCE_MONTH as P8, ");
            sqlBuff.append("T2.GS_FEE_TYPE as P9,T1.GS_OFFSET_DATE as P10 from ");
            sqlBuff.append("(select a.gs_receiveable_id ,a.gs_workcorp_id ,b.gs_provider_name ,GS_PAY_TYPE ,a.GS_FEE_TYPE_ID ,");
            sqlBuff.append("a.GS_BALANCE_UNDONE ,a.GS_PAY_DATE ,GS_PRINCIPAL ,GS_BALANCE_MONTH ,GS_OFFSET_DATE ");
            sqlBuff.append("from GS_PAY_RECEIVE_ABLE a,GS_PROVIDER b ");
            sqlBuff.append("where a.gs_fee_type_id <> '990' and a.gs_fee_type_id <>'991' and a.GS_WORKCORP_ID=b.gs_provider_id and GS_PAY_STATUS<>'3' and b.gs_provider_shortname = '"+vo.provName+"' ");
            sqlBuff.append(") T1 LEFT JOIN GS_balance_fee T2 ON T1.GS_FEE_TYPE_ID = T2.GS_fee_type_no ");
            sqlBuff.append("order by GS_PAY_DATE ASC, T1.GS_OFFSET_DATE ASC");
        }
        GSLogWriter.info(sqlBuff.toString());
        try{
            GSPayReceiveAbleDAO prd = new GSPayReceiveAbleDAO();
            ary = prd.queryPayReceiveAble(sqlBuff.toString());
        }catch(Exception e){
            throw new GSException (GSException.E20080,GSException.E20080_MSG);
        }
        
        return ary;
    }

    /**
     * 进行自动过账时，调用本方法
     * @param vo
     * @throws GSException
     */
    public void doAutoPost(GSOperatorBean bean,GSPayReceiveSaveVO vo) throws GSException {
        
        GSPayReceiveAbleDAO prd = new GSPayReceiveAbleDAO();
        Connection conn = null;    
        try{
            conn = prd.getConnetion();
            conn.setAutoCommit(false);
            String curDate = DateUtil.getCurrentDate("yyyy-MM-dd HH:mm:ss");
            doAutoPost(bean,prd,conn,vo,curDate);
            conn.commit();
        }catch(Exception e){
            try{
                conn.rollback();
            }catch(Exception sqle){}
            throw new GSException(GSException.E20081,GSException.E20081_MSG);
	    }finally{	  
	        try{
                conn.setAutoCommit(true);
            }catch(Exception sqle){}
	        GSPayReceiveAbleDAO.close(conn);
	    }
    }
    
    /**
     * 在进行过账时，直接调用本方法
     * @param prd
     * @param conn
     * @param vo
     * @throws Exception
     */
    private ArrayList[] doAutoPost(GSOperatorBean operBean,GSPayReceiveAbleDAO prd ,Connection conn ,GSPayReceiveSaveVO vo,String curDate) throws Exception {
        
        String tSql = "select * from gs_pay_receive_able where gs_workcorp_id = ? and gs_workcorp_category = ? and gs_pay_status <> 3 order by GS_PAY_DATE ASC,GS_OFFSET_DATE ASC";

        Object[] tObj = {vo.deptcode,vo.workcorpcategory};
        ArrayList ary = prd.executeQuery(new GSPayReceiveAbleBean(),tSql,tObj);
        if(ary == null || ary.size() == 0)
            return new ArrayList[]{null,null};
        double numPlus = 0;double numMinus = 0;
        ArrayList plusAry = new ArrayList();
        ArrayList minusAry = new ArrayList();
        for(int i=0;i<ary.size();i++){
            GSPayReceiveAbleBean bean = (GSPayReceiveAbleBean)ary.get(i);
            if(("1".equals(bean.getGs_pay_type()) && !"990".equals(bean.getGs_fee_type_id())) || ("2".equals(bean.getGs_pay_type()) && "991".equals(bean.getGs_fee_type_id()))){
                numPlus += bean.getGs_balance_undone();
                plusAry.add(bean);//待收款列表(包括应收和预付)
            }   
            if(("2".equals(bean.getGs_pay_type()) && !"991".equals(bean.getGs_fee_type_id())) || ("1".equals(bean.getGs_pay_type()) && "990".equals(bean.getGs_fee_type_id()))){
                numMinus += bean.getGs_balance_undone();
                minusAry.add(bean);//待付款列表(包括应付和预收)
            }   
        }
        
        
        if(numPlus == numMinus){
            //平帐
            String tSql1 = "update gs_pay_receive_able set GS_PAY_STATUS=3, " +
	        		"GS_BALANCE_UNDONE=0, GS_BALANCE_DONE=GS_BALANCE_NEED, GS_OFFSET_DATE=? " +
	        		"where GS_WORKCORP_ID=? ";
            PreparedStatement pstmt1 = null;
            pstmt1 = conn.prepareStatement(tSql1);
            pstmt1.setString(1,curDate);
            pstmt1.setString(2,vo.deptcode);
            pstmt1.execute();
            pstmt1.close();
            plusAry = null;minusAry = null;
            
        }else if(numPlus > numMinus && numMinus != 0){//numPlus > numMinus,待收多于待付
            String tSql1 = "update gs_pay_receive_able set GS_PAY_STATUS=3, " +
            		"GS_BALANCE_UNDONE=0, GS_BALANCE_DONE=GS_BALANCE_NEED, GS_OFFSET_DATE=? " +
            		"where GS_RECEIVEABLE_ID=?";
            String tSql2 = "update gs_pay_receive_able set GS_PAY_STATUS=2, " +
            		"GS_BALANCE_UNDONE=(GS_BALANCE_UNDONE-?), GS_BALANCE_DONE=(GS_BALANCE_DONE+?), GS_OFFSET_DATE=? " +
            		"where GS_RECEIVEABLE_ID=? ";
            PreparedStatement pstmt1 = null;
            PreparedStatement pstmt2 = null;
            //对类型为2(付款)的，将所有的paystatus置为3,undone置为0,done置为need
            pstmt1 = conn.prepareStatement(tSql1);
            for(int i=0;i<minusAry.size();i++){
                GSPayReceiveAbleBean bean = (GSPayReceiveAbleBean)minusAry.get(i);
                pstmt1.setString(1,curDate);
                pstmt1.setString(2,bean.getGs_receiveable_id());
                pstmt1.addBatch();
            }
            pstmt1.executeBatch();
            pstmt1.close();
            minusAry = null;
            
            
            //对类型为1(付款)的，执行单条过账
            pstmt1 = conn.prepareStatement(tSql1);
            pstmt2 = conn.prepareStatement(tSql2);
            for(int i=0;i<plusAry.size();i++){                
                GSPayReceiveAbleBean bean = (GSPayReceiveAbleBean)plusAry.get(i);
                if(bean.getGs_balance_undone() <= numMinus){//当前须过账金额<=num余额
                    pstmt1.setString(1,curDate);
                    pstmt1.setString(2,bean.getGs_receiveable_id());
                    pstmt1.addBatch(); 
                    numMinus -= bean.getGs_balance_undone();//num余额 -= 当前须过账金额

                    plusAry.set(i,null);
                }else{//当前须过账金额>num余额
                    pstmt2.setDouble(1,numMinus);
                    pstmt2.setDouble(2,numMinus);
                    pstmt2.setString(3,curDate);
                    pstmt2.setString(4,bean.getGs_receiveable_id());
                    pstmt2.addBatch();
                    bean.setGs_balance_undone(bean.getGs_balance_undone() - numMinus);
                    numMinus = 0;//num余额归零
                    
                }
                if(numMinus == 0) break;//如果num余额为零，则后面的不再继续处理
            }
            pstmt1.executeBatch();pstmt1.close();
            pstmt2.executeBatch();pstmt2.close();
        }else if(numPlus < numMinus && numMinus != 0) {//numPlus < numMinus待付多于待收
            String tSql1 = "update gs_pay_receive_able set GS_PAY_STATUS=3, " +
	        		"GS_BALANCE_UNDONE=0, GS_BALANCE_DONE=GS_BALANCE_NEED, GS_OFFSET_DATE=? " +
	        		"where GS_RECEIVEABLE_ID=?";
	        String tSql2 = "update gs_pay_receive_able set GS_PAY_STATUS=2, " +
	        		"GS_BALANCE_UNDONE=(GS_BALANCE_UNDONE-?), GS_BALANCE_DONE=(GS_BALANCE_DONE+?), GS_OFFSET_DATE=? " +
	        		"where GS_RECEIVEABLE_ID=?";
	        PreparedStatement pstmt1 = null;
	        PreparedStatement pstmt2 = null;
	        //对类型为1(收款)的，将所有的paystatus置为3,undone置为0,done置为need
	        pstmt1 = conn.prepareStatement(tSql1);
	        for(int i=0;i<plusAry.size();i++){
	            GSPayReceiveAbleBean bean = (GSPayReceiveAbleBean)plusAry.get(i);
	            pstmt1.setString(1,curDate);
	            pstmt1.setString(2,bean.getGs_receiveable_id());
	            pstmt1.addBatch();
	        }
	        pstmt1.executeBatch();
	        pstmt1.close();
	        plusAry = null;
	        

            //对类型为1(付款)的，执行单条过账
	        pstmt1 = conn.prepareStatement(tSql1);
	        pstmt2 = conn.prepareStatement(tSql2);
	        for(int i=0;i<minusAry.size();i++){
	            GSPayReceiveAbleBean bean = (GSPayReceiveAbleBean)minusAry.get(i);
	            if(bean.getGs_balance_undone() <= numPlus){//当前须过账金额<=num余额
	                pstmt1.setString(1,curDate);
	                pstmt1.setString(2,bean.getGs_receiveable_id());
	                pstmt1.addBatch(); 
	                numPlus -= bean.getGs_balance_undone();//num余额 -= 当前须过账金额
	                
	                minusAry.set(i,null);
	            }else{//当前须过账金额>num余额
	                pstmt2.setDouble(1,numPlus);
	                pstmt2.setDouble(2,numPlus);
	                pstmt2.setString(3,curDate);
	                pstmt2.setString(4,bean.getGs_receiveable_id());
	                pstmt2.addBatch();
	                bean.setGs_balance_undone(bean.getGs_balance_undone() - numPlus);
	                numPlus = 0;//num余额归零

	            }
	            if(numPlus == 0) break;//如果num余额为零，则后面的不再继续处理
	        }
	        pstmt1.executeBatch();pstmt1.close();
	        pstmt2.executeBatch();pstmt2.close();
            
        }
            
        return new ArrayList[]{plusAry,minusAry};
    }
    
    public void doAutoStrike(GSOperatorBean operBean,GSPayReceiveSaveVO vo) throws GSException {
        
        GSPayReceiveAbleDAO prd = new GSPayReceiveAbleDAO();
        Connection conn = null;    
        try{
            conn = prd.getConnetion();
            conn.setAutoCommit(false);
            String curDate = vo.payDate + " " + DateUtil.getCurrentDate("HH:mm:ss");
            //先执行自动过账
            ArrayList[] aryLists = doAutoPost(operBean,prd,conn,vo,curDate);
            ArrayList ary1 = aryLists[0];ArrayList ary2 = aryLists[1];
            //自动冲销
            double strikeNum = 0;
            if(ary1 == null)
                strikeNum = doAutoStrike(operBean,prd,conn,vo,ary2,curDate);
            else 
                strikeNum = doAutoStrike(operBean,prd,conn,vo,ary1,curDate);
            //记录收付款表
			GSPayReceiveMentBean prmBean = new GSPayReceiveMentBean();
			prmBean.setGs_actural_pay_num(Double.parseDouble(vo.payreceiveNum));
			prmBean.setGs_offset_total_num(0);
			prmBean.setGs_functionary(vo.principal);
			prmBean.setGs_invoice(vo.invoiceMsg.equals("K")?"1":"2");
			prmBean.setGs_invoice_code(vo.invoiceNo);
			prmBean.setGs_invoice_pay_date(curDate);
			prmBean.setGs_notes(vo.notes);
			//prmBean.setGs_offset_total_num(Double.parseDouble(vo.payreceiveNum));
			prmBean.setGs_pay_category(vo.payreceiveType);
			prmBean.setGs_pay_workcorp_category(vo.workcorpcategory);
			prmBean.setGs_pay_workcorp_id(vo.deptcode);
			this.insertPayReceiveMent(conn,prmBean);
            
			conn.commit();
            
        }catch(GSException gse){
            try{
                conn.rollback();
            }catch(Exception sqle){}
            throw gse;
	    }catch(Exception e){
	    	e.printStackTrace();
            try{
                conn.rollback();
            }catch(Exception sqle){}
            throw new GSException(GSException.E20082,GSException.E20082_MSG);
	    }finally{
	        try{
                conn.setAutoCommit(true);
            }catch(Exception sqle){}
	        GSPayReceiveAbleDAO.close(conn);
	    }        
    }
    
    private double doAutoStrike(GSOperatorBean operBean,GSPayReceiveAbleDAO prd ,Connection conn ,GSPayReceiveSaveVO vo,ArrayList aryTemp,String curDate) throws Exception {
        //得到一个当前的未冲销列表，该列表中的paytype应该为统一的值
        //String tSql = "select * from gs_pay_receive_able where gs_workcorp_id = ? and gs_workcorp_category = ? " +
        //		"and gs_pay_status <> 3 order by GS_PAY_DATE";
        //Object[] tObj = {vo.deptcode,vo.workcorpcategory};
        //ArrayList ary = prd.executeQuery(new GSPayReceiveAbleBean(),tSql,tObj);

        if(aryTemp == null || aryTemp.size() == 0)
            return 0;
        ArrayList ary = new ArrayList();
        GSPayReceiveAbleBean tmpBean = null;
        //过滤列表中的空值，这些空值代表已经过账完成的纪录
        double totalValue = 0;
        for(int i=0;i<aryTemp.size();i++){
            tmpBean = (GSPayReceiveAbleBean)aryTemp.get(i);
            //如果列表中有预付费的,不列入需冲销的列表
            if(tmpBean != null && !"990".equals(tmpBean.getGs_fee_type_id()) && !"991".equals(tmpBean.getGs_fee_type_id())){
                ary.add(tmpBean);
            }
        }
        tmpBean = (GSPayReceiveAbleBean)ary.get(0);
        //如果输入的收付款类型与当前需要的收付款类型不一致，则不做任何冲销动作，只生成预收付款信息，写应收应付信息表
        //此时不必写收付款表
        if(!tmpBean.getGs_pay_type().equals(vo.payreceiveType)){

            PreparedStatement pstmt = conn.prepareStatement(tPayReceiveAbleSql);
            pstmt.setString(1,vo.deptcode);
            pstmt.setString(2,vo.workcorpcategory);
            pstmt.setString(3,DateUtil.getCurrentDate("yyyyMM"));
            pstmt.setString(4,"");
            pstmt.setString(5,vo.deptcode.indexOf("00")==-1?"2":"1");
            if("1".equals(vo.workcorpcategory)){
                GSOrgProcessor orgp = new GSOrgProcessor();
                String upperOrgId = orgp.getOrgByCode(vo.deptcode).getGs_upper_orgId();
                pstmt.setString(6,upperOrgId+"-"+vo.deptcode);                
                
            }else{
                pstmt.setString(6,vo.deptcode);
            }
            pstmt.setString(7,"1".equals(vo.payreceiveType)?"990":"991");
            pstmt.setString(8,vo.payDate);
            pstmt.setString(9,"1".equals(vo.payreceiveType)?"1":"2");
            pstmt.setDouble(10,Double.parseDouble(vo.payreceiveNum));
            pstmt.setDouble(11,0);
            pstmt.setDouble(12,Double.parseDouble(vo.payreceiveNum));
            //SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            //pstmt.setString(13,sdf.format(new Date(System.currentTimeMillis())));
            pstmt.setString(13,curDate);
            pstmt.setString(14,"1");
            pstmt.setString(15,vo.principal);
            pstmt.setString(16,operBean.getGs_oper_id());
            pstmt.setString(17,curDate);
            pstmt.setString(18,vo.invoiceNo);//空格用于分隔多次冲销的发票号
            pstmt.setString(19,GenRandNum.genDTRandNum());
            pstmt.executeUpdate();
        }
        //否则，按顺序执行冲销
        else{
            String tSql1 = "update gs_pay_receive_able set GS_PAY_STATUS=3, " +
            		"GS_BALANCE_UNDONE=0, GS_BALANCE_DONE=GS_BALANCE_NEED, GS_OFFSET_DATE=?, GS_INVOICE_CODE=GS_INVOICE_CODE||' '||? " +
    				"where GS_RECEIVEABLE_ID=?";
            String tSql2 = "update gs_pay_receive_able set GS_PAY_STATUS=2, " +
            		"GS_BALANCE_UNDONE=(GS_BALANCE_UNDONE-?), GS_BALANCE_DONE=(GS_BALANCE_DONE+?), GS_OFFSET_DATE=?, GS_INVOICE_CODE=GS_INVOICE_CODE||' '||? " +
    				"where GS_RECEIVEABLE_ID=?";

            PreparedStatement pstmt1 = conn.prepareStatement(tSql1);
            PreparedStatement pstmt2 = conn.prepareStatement(tSql2);
            double tempV = Double.parseDouble(vo.payreceiveNum);
            for(int i=0;i<ary.size();i++){
                GSPayReceiveAbleBean tBean = (GSPayReceiveAbleBean)ary.get(i);
                if(tBean.getGs_balance_undone() <= tempV){
                    pstmt1.setString(1,curDate);
                    pstmt1.setString(2,vo.invoiceNo);
                    pstmt1.setString(3,tBean.getGs_receiveable_id());
                    pstmt1.addBatch();
                    tempV -= tBean.getGs_balance_undone();
                    
                    
                }else{
                    pstmt2.setDouble(1,tempV);
                    pstmt2.setDouble(2,tempV);
                    pstmt2.setString(3,curDate);
                    pstmt2.setString(4,vo.invoiceNo);
                    pstmt2.setString(5,tBean.getGs_receiveable_id());
                    pstmt2.addBatch();
                    tempV = 0;

                
                }
                if(tempV == 0){
                    totalValue = Double.parseDouble(vo.payreceiveNum);
                    break;
                }
                    
            }
            pstmt2.executeBatch();
            pstmt2.close();
            
            pstmt1.executeBatch();
            pstmt1.close();
            
            
            //如果有多余的录入金额没有冲销完，则在应收应付表中增加一条预收(付)款记录
            //此时不须记录收付款表
            if(tempV > 0){
                PreparedStatement pstmt = conn.prepareStatement(tPayReceiveAbleSql);
                pstmt.setString(1,vo.deptcode);
                pstmt.setString(2,vo.workcorpcategory);
                pstmt.setString(3,DateUtil.getCurrentDate("yyyyMM"));
                pstmt.setString(4,"");
                pstmt.setString(5,vo.deptcode.indexOf("00")==-1?"2":"1");
                if("1".equals(vo.workcorpcategory)){
                    GSOrgProcessor orgp = new GSOrgProcessor();
                    String upperOrgId = orgp.getOrgByCode(vo.deptcode).getGs_upper_orgId();
                    pstmt.setString(6,upperOrgId+"-"+vo.deptcode);                
                    
                }else{
                    pstmt.setString(6,vo.deptcode);
                }
                pstmt.setString(7,"1".equals(vo.payreceiveType)?"990":"991");
                pstmt.setString(8,vo.payDate);
                pstmt.setString(9,"1".equals(vo.payreceiveType)?"1":"2");
                pstmt.setDouble(10,tempV);
                pstmt.setDouble(11,0);
                pstmt.setDouble(12,tempV);
                //SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                //pstmt.setString(13,sdf.format(new Date(System.currentTimeMillis())));
                pstmt.setString(13,curDate);
                pstmt.setString(14,"1");
                pstmt.setString(15,vo.principal);
                pstmt.setString(16,operBean.getGs_oper_id());
                pstmt.setString(17,curDate);
                pstmt.setString(18,vo.invoiceNo);//空格用于分隔多次冲销的发票号
                pstmt.setString(19,GenRandNum.genDTRandNum());
                pstmt.executeUpdate();
                pstmt.close();
                
                totalValue = Double.parseDouble(vo.payreceiveNum) - tempV;
            }
        }
        
        return totalValue;
    }
    
    
    public void doHandStrike(GSOperatorBean operBean,GSPayReceiveSaveVO vo) throws GSException {
        GSPayReceiveAbleDAO prd = new GSPayReceiveAbleDAO();
        Connection conn = null;    
        try{
            conn = prd.getConnetion();
            conn.setAutoCommit(false);
            
            String[] idList = vo.payreceiveID;
            ArrayList ary = prd.queryPayReceiveAble(conn,idList);
            String curDate = vo.payDate + " " + DateUtil.getCurrentDate("HH:mm:ss");
            //对所选择的记录先自动过帐,得到未过帐的记录
            ArrayList ary_ = doAutoPostForHandStrike(operBean,prd,conn,vo,ary,curDate);
            //对剩余的未过帐纪录执行手动冲销
            doHandStrike(operBean,prd,conn,vo,ary_,curDate);
            //记录收付款表
			GSPayReceiveMentBean prmBean = new GSPayReceiveMentBean();
			prmBean.setGs_actural_pay_num(Double.parseDouble(vo.payreceiveNum));
			prmBean.setGs_offset_total_num(Double.parseDouble(vo.strikeNum));
			prmBean.setGs_functionary(vo.principal);
			prmBean.setGs_invoice(vo.invoiceMsg.equals("K")?"1":"2");
			prmBean.setGs_invoice_code(vo.invoiceNo);
			prmBean.setGs_invoice_pay_date(curDate);
			prmBean.setGs_notes(vo.notes);
			prmBean.setGs_pay_category(vo.payreceiveType);
			prmBean.setGs_pay_workcorp_category(vo.workcorpcategory);
			prmBean.setGs_pay_workcorp_id(vo.deptcode);
			this.insertPayReceiveMent(conn,prmBean);
            conn.commit();
			//conn.rollback();
        }catch(GSException gse){
            try{
                conn.rollback();
            }catch(Exception sqle){}
            throw gse;
	    }catch(Exception e){
            try{
                conn.rollback();
            }catch(Exception sqle){}
            throw new GSException(GSException.E20083,GSException.E20083_MSG);
	    }finally{	 
	        try{
                conn.setAutoCommit(true);
            }catch(Exception sqle){}
	        GSPayReceiveAbleDAO.close(conn);
	    }   
    }

    /**
     * 手工冲销时,先调用本方法对选中的记录实行自动过账
     * @param prd
     * @param conn
     * @param vo
     * @throws Exception
     */
    private ArrayList doAutoPostForHandStrike(GSOperatorBean operBean,GSPayReceiveAbleDAO prd ,Connection conn ,GSPayReceiveSaveVO vo,ArrayList ary,String curDate) throws Exception {
        
        if(ary == null || ary.size() == 0)
            return null;
        double numPlus = 0;double numMinus = 0;
        ArrayList plusAry = new ArrayList();
        ArrayList minusAry = new ArrayList();
        for(int i=0;i<ary.size();i++){
            GSPayReceiveAbleBean bean = (GSPayReceiveAbleBean)ary.get(i);
            if(("1".equals(bean.getGs_pay_type()) && !"990".equals(bean.getGs_fee_type_id())) || ("2".equals(bean.getGs_pay_type()) && "991".equals(bean.getGs_fee_type_id()))){
                numPlus += bean.getGs_balance_undone();
                plusAry.add(bean);//待收款列表(包括应收和预付)
            }   
            if(("2".equals(bean.getGs_pay_type()) && !"991".equals(bean.getGs_fee_type_id())) || ("1".equals(bean.getGs_pay_type()) && "990".equals(bean.getGs_fee_type_id()))){
                numMinus += bean.getGs_balance_undone();
                minusAry.add(bean);//待付款列表(包括应付和预收)
            }   
            
        }
        
        if(numPlus > numMinus && numMinus != 0){//numPlus > numMinus
            String tSql1 = "update gs_pay_receive_able set GS_PAY_STATUS=3, " +
            		"GS_BALANCE_UNDONE=0, GS_BALANCE_DONE=GS_BALANCE_NEED, GS_OFFSET_DATE=? " +
            		"where GS_RECEIVEABLE_ID=?";
            String tSql2 = "update gs_pay_receive_able set GS_PAY_STATUS=2, " +
            		"GS_BALANCE_UNDONE=(GS_BALANCE_UNDONE-?), GS_BALANCE_DONE=(GS_BALANCE_DONE+?), GS_OFFSET_DATE=? " +
            		"where GS_RECEIVEABLE_ID=? ";
            PreparedStatement pstmt1 = null;
            PreparedStatement pstmt2 = null;
            //对类型为2(付款)的，将所有的paystatus置为3,undone置为0,done置为need
            pstmt1 = conn.prepareStatement(tSql1);
            for(int i=0;i<minusAry.size();i++){
                GSPayReceiveAbleBean bean = (GSPayReceiveAbleBean)minusAry.get(i);
                pstmt1.setString(1,curDate);
                pstmt1.setString(2,bean.getGs_receiveable_id());
                pstmt1.addBatch();
            }
            pstmt1.executeBatch();
            pstmt1.close();
            minusAry = null;
            
            //对类型为1(付款)的，执行单条过账
            pstmt1 = conn.prepareStatement(tSql1);
            pstmt2 = conn.prepareStatement(tSql2);
            for(int i=0;i<plusAry.size();i++){
                GSPayReceiveAbleBean bean = (GSPayReceiveAbleBean)plusAry.get(i);
                if(bean.getGs_balance_undone() <= numMinus){//当前须过账金额<=num余额
                    pstmt1.setString(1,curDate);
                    pstmt1.setString(2,bean.getGs_receiveable_id());
                    pstmt1.addBatch(); 
                    numMinus -= bean.getGs_balance_undone();//num余额 -= 当前须过账金额
                    plusAry.set(i,null);
                }else{//当前须过账金额>num余额
                    pstmt2.setDouble(1,numMinus);
                    pstmt2.setDouble(2,numMinus);
                    pstmt2.setString(3,curDate);
                    pstmt2.setString(4,bean.getGs_receiveable_id());
                    pstmt2.addBatch();
                    bean.setGs_balance_undone(bean.getGs_balance_undone() - numMinus);
                    numMinus = 0;//num余额归零
                }
                if(numMinus == 0) break;//如果num余额为零，则后面的不再继续处理
            }
            pstmt1.executeBatch();pstmt1.close();
            pstmt2.executeBatch();pstmt2.close();
        }else if(numPlus < numMinus && numPlus != 0 ){//numPlus < numMinus
            String tSql1 = "update gs_pay_receive_able set GS_PAY_STATUS=3, " +
	        		"GS_BALANCE_UNDONE=0, GS_BALANCE_DONE=GS_BALANCE_NEED, GS_OFFSET_DATE=? " +
	        		"where GS_RECEIVEABLE_ID=?";
	        String tSql2 = "update gs_pay_receive_able set GS_PAY_STATUS=2, " +
	        		"GS_BALANCE_UNDONE=(GS_BALANCE_UNDONE-?), GS_BALANCE_DONE=(GS_BALANCE_DONE+?), GS_OFFSET_DATE=? " +
	        		"where GS_RECEIVEABLE_ID=?";
	        PreparedStatement pstmt1 = null;
	        PreparedStatement pstmt2 = null;
	        //对类型为1(收款)的，将所有的paystatus置为3,undone置为0,done置为need
	        pstmt1 = conn.prepareStatement(tSql1);
	        for(int i=0;i<plusAry.size();i++){
	            GSPayReceiveAbleBean bean = (GSPayReceiveAbleBean)plusAry.get(i);
	            pstmt1.setString(1,curDate);
	            pstmt1.setString(2,bean.getGs_receiveable_id());
	            pstmt1.addBatch();
	        }
	        pstmt1.executeBatch();
	        pstmt1.close();
	        plusAry = null;
	        
            //对类型为1(付款)的，执行单条过账
	        pstmt1 = conn.prepareStatement(tSql1);
	        pstmt2 = conn.prepareStatement(tSql2);
	        for(int i=0;i<minusAry.size();i++){
	            GSPayReceiveAbleBean bean = (GSPayReceiveAbleBean)minusAry.get(i);
	            if(bean.getGs_balance_undone() <= numPlus){//当前须过账金额<=num余额
	                pstmt1.setString(1,curDate);
	                pstmt1.setString(2,bean.getGs_receiveable_id());
	                pstmt1.addBatch(); 
	                numPlus -= bean.getGs_balance_undone();//num余额 -= 当前须过账金额
	                minusAry.set(i,null);
	            }else{//当前须过账金额>num余额
	                pstmt2.setDouble(1,numPlus);
	                pstmt2.setDouble(2,numPlus);
	                pstmt2.setString(3,curDate);
	                pstmt2.setString(4,bean.getGs_receiveable_id());
	                pstmt2.addBatch();
	                bean.setGs_balance_undone(bean.getGs_balance_undone() - numPlus);
	                numPlus = 0;//num余额归零
	            }
	            if(numPlus == 0) break;//如果num余额为零，则后面的不再继续处理
	        }
	        pstmt1.executeBatch();pstmt1.close();
	        pstmt2.executeBatch();pstmt2.close();
        }
        //如果某种支付方式已经全部过帐完成,则该过账方式的矢量置null
        return plusAry!=null?plusAry:minusAry;
    }

    
    private void doHandStrike(GSOperatorBean operBean,GSPayReceiveAbleDAO prd ,Connection conn ,GSPayReceiveSaveVO vo,ArrayList ary,String curDate) throws Exception {
        String tSql1 = "update gs_pay_receive_able set GS_PAY_STATUS='3', " +
				"GS_BALANCE_UNDONE=0, GS_BALANCE_DONE=GS_BALANCE_NEED, GS_OFFSET_DATE=?, GS_INVOICE_CODE=GS_INVOICE_CODE||'_'||? " +
				"where GS_RECEIVEABLE_ID=?";
		String tSql2 = "update gs_pay_receive_able set GS_PAY_STATUS='2', " +
				"GS_BALANCE_UNDONE=(GS_BALANCE_UNDONE-?), GS_BALANCE_DONE=(GS_BALANCE_DONE+?), " +
				"GS_OFFSET_DATE=?, GS_INVOICE_CODE=GS_INVOICE_CODE||'_'||? " +
				"where GS_RECEIVEABLE_ID=?";
		
		PreparedStatement pstmt1 = conn.prepareStatement(tSql1);
		PreparedStatement pstmt2 = conn.prepareStatement(tSql2);
		double tempV = Double.parseDouble(vo.payreceiveNum);
		//double totalValue = 0;
		//累加需要冲销的余额
		//for(int i=0;i<ary.size();i++){
		//    GSPayReceiveAbleBean tBean = (GSPayReceiveAbleBean)ary.get(i);
		//    totalValue += tBean.getGs_balance_undone();
		//}
		boolean flag = false;
		for(int i=0;i<ary.size();i++){
		    GSPayReceiveAbleBean tBean = (GSPayReceiveAbleBean)ary.get(i);
		    if(vo.payreceiveType.equals(tBean.getGs_pay_type())){
			    if(tBean.getGs_balance_undone() <= tempV){
			        pstmt1.setString(1,curDate);
			        pstmt1.setString(2,vo.invoiceNo);
			        pstmt1.setString(3,tBean.getGs_receiveable_id());
			        pstmt1.addBatch();
			        tempV -= tBean.getGs_balance_undone();
		        
			    }else{
			        pstmt2.setDouble(1,tempV);
			        pstmt2.setDouble(2,tempV);
			        pstmt2.setString(3,curDate);
			        pstmt2.setString(4,vo.invoiceNo);
			        pstmt2.setString(5,tBean.getGs_receiveable_id());
			        //pstmt2.addBatch();
			        tempV = 0;
			        flag = true;//计数变量，判断pstmt2是否关联参数

			    }
		    }else{
		        //do nothing
		    }
		    if(tempV == 0)
		        break;
		}
		pstmt1.executeBatch();
		pstmt1.close();
		if(flag){
			pstmt2.executeUpdate();				
		}
		pstmt2.close();
		
		//如果有多余的录入金额没有冲销完，则在应收应付表中增加一条预收(付)款记录
		//此时不须记录收付款表
		if(tempV > 0){
		    PreparedStatement pstmt = conn.prepareStatement(tPayReceiveAbleSql);
		    pstmt.setString(1,vo.deptcode);
		    pstmt.setString(2,vo.workcorpcategory);
		    pstmt.setString(3,DateUtil.getCurrentDate("yyyyMM"));
		    pstmt.setString(4,"");
		    pstmt.setString(5,vo.deptcode.indexOf("00")==-1?"2":"1");
		    if("1".equals(vo.workcorpcategory)){
		        GSOrgProcessor orgp = new GSOrgProcessor();
		        String upperOrgId = orgp.getOrgByCode(vo.deptcode).getGs_upper_orgId();
		        pstmt.setString(6,upperOrgId+"-"+vo.deptcode);                
		        
		    }else{
		        pstmt.setString(6,vo.deptcode);
		    }
            pstmt.setString(7,"1".equals(vo.payreceiveType)?"990":"991");
            pstmt.setString(8,vo.payDate);
            pstmt.setString(9,"1".equals(vo.payreceiveType)?"1":"2");
		    pstmt.setDouble(10,tempV);
		    pstmt.setDouble(11,0);
		    pstmt.setDouble(12,tempV);
            //SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            //pstmt.setString(13,sdf.format(new Date(System.currentTimeMillis())));
		    pstmt.setString(13,curDate);
		    pstmt.setString(14,"1");
		    pstmt.setString(15,vo.principal);
		    pstmt.setString(16,operBean.getGs_oper_id());
		    pstmt.setString(17,curDate);
		    pstmt.setString(18,vo.invoiceNo);
		    pstmt.setString(19,GenRandNum.genDTRandNum());
		    pstmt.executeUpdate();
		    pstmt.close();
		}
        
		//return totalValue;
    }
    
    private void insertPayReceiveMent(Connection conn, GSPayReceiveMentBean tBean) throws GSException {
        String tPayReceiveMentSql = "insert into gs_pay_receive_ment (GS_PAY_WORKCORP_ID,GS_PAY_WORKCORP_CATEGORY," +
		"GS_PAY_CATEGORY,GS_ACTUAL_PAY_DATE,GS_ACTUAL_PAY_NUM,GS_OFFSET_TOTAL_NUM," +
		"GS_INVOICE,GS_INVOICE_CODE,GS_FUNCTIONARY,GS_NOTES) values (?,?,?,?,?,?,?,?,?,?)";
        try{
	        PreparedStatement statement = conn.prepareStatement(tPayReceiveMentSql);
	        statement.setString(1,tBean.getGs_pay_workcorp_id());
	        statement.setString(2,tBean.getGs_pay_workcorp_category());
	        statement.setString(3,tBean.getGs_pay_category());
	        statement.setString(4,tBean.getGs_invoice_pay_date());
	        statement.setDouble(5,tBean.getGs_actural_pay_num());
	        statement.setDouble(6,tBean.getGs_offset_total_num());
	        statement.setString(7,tBean.getGs_invoice());
	        statement.setString(8,tBean.getGs_invoice_code());
	        statement.setString(9,tBean.getGs_functionary());
	        statement.setString(10,tBean.getGs_notes());
	        
	        statement.executeUpdate();
	        statement.close();
        
        }catch(Exception ex){
            throw new GSException(GSException.E20084,GSException.E20084_MSG);
        }
    }
    
    
    public ArrayList getPayReceiveAlertList(GSOperatorBean operatorBean,GSPayReceiveAlertQueryVO vo,int pageNo,int pageSize) throws GSException {
        ArrayList ary = null;
        StringBuffer tSql = getAlertSql(operatorBean,vo);
        try{
            GSPayReceiveAbleDAO prd = new GSPayReceiveAbleDAO();
            ary = prd.executeQuery(new GSPayReceiveAlertListVO(),tSql.toString(),null,pageNo,pageSize);
        }catch(Exception e){
            throw new GSException (GSException.E20080,GSException.E20080_MSG);
        }
        
        return ary;

    }
    
    public int getPayReceiveAlertListCount(GSOperatorBean operatorBean,GSPayReceiveAlertQueryVO vo) throws GSException {
        int count = 0;
        StringBuffer tSqlBuffer = getAlertSql(operatorBean,vo);
        String tSql = "select count(*) from(" + tSqlBuffer.toString() + ")";
        try{
            GSPayReceiveAbleDAO prd = new GSPayReceiveAbleDAO();
            count = prd.queryRowNum(tSql,null);
        }catch(Exception e){
            throw new GSException (GSException.E20080,GSException.E20080_MSG);
        }
        
        return count;

    }
    
    public StringBuffer getAlertSql(GSOperatorBean operatorBean,GSPayReceiveAlertQueryVO vo){
        StringBuffer tSql = new StringBuffer("");

        //公司名称 公司类型 费用类型 类型 金额（元） 已完成金额（元） 剩余金额（元） 到期日期 联系人 状态
//        try{
//	        GSProviderDAO tPDAO = new GSProviderDAO();
//	        String CCZXID = GSSystemConfigurator.getParam("gs.provider.cczxid");
//	        String CCZXName = tPDAO.queryProvider(CCZXID).getGs_provider_shortname();
//        }catch(GSException ex){
//        	
//        }
        
        int workcorpcategory = Integer.parseInt(vo.workcorpcategory);
        switch(workcorpcategory){
        case 1:
            tSql.append("select ut.deptname as P1,ut.gs_workcorp_category as P2,ut.GS_FEE_TYPE_ID as P3,");
            tSql.append("ut.gs_pay_type as P4,ut.GS_BALANCE_NEED as P5,ut.GS_BALANCE_DONE as P6,");
            tSql.append("ut.GS_BALANCE_UNDONE as P7,ut.GS_PAY_DATE as P8,ut.GS_PRINCIPAL as P9, ");
            tSql.append("ut.GS_PAY_STATUS as P10,bc.gs_fee_type as P11 ");
            tSql.append("from(");
            
            tSql.append("select b.gs_org_name as deptname,a.gs_workcorp_category ,a.GS_FEE_TYPE_ID ,");
            tSql.append("a.gs_pay_type ,a.GS_BALANCE_NEED ,a.GS_BALANCE_DONE ,");
            tSql.append("a.GS_BALANCE_UNDONE ,a.GS_PAY_DATE ,a.GS_PRINCIPAL ,a.GS_PAY_STATUS  ");
            tSql.append(" from gs_pay_receive_able a,gs_organization b where 1=1 and a.gs_workcorp_id = b.gs_org_id ");
            if(vo.deptid != null && !"".equals(vo.deptid)){
                tSql.append(" and a.gs_workcorp_id = '"+vo.deptid+"' ");    
            }            
            tSql.append(" ) ut");
            break;
        case 2:
            tSql.append("select ut.deptname as P1,ut.gs_workcorp_category as P2,ut.GS_FEE_TYPE_ID as P3,");
            tSql.append("ut.gs_pay_type as P4,ut.GS_BALANCE_NEED as P5,ut.GS_BALANCE_DONE as P6,");
            tSql.append("ut.GS_BALANCE_UNDONE as P7,ut.GS_PAY_DATE as P8,ut.GS_PRINCIPAL as P9,");
            tSql.append("ut.GS_PAY_STATUS as P10,bc.gs_fee_type as P11 ");
            tSql.append("from(");
            
            tSql.append("select b.gs_provider_shortname as deptname ,a.gs_workcorp_category ,a.GS_FEE_TYPE_ID ,");
            tSql.append("a.gs_pay_type ,a.GS_BALANCE_NEED ,a.GS_BALANCE_DONE ,");
            tSql.append("a.GS_BALANCE_UNDONE ,a.GS_PAY_DATE ,a.GS_PRINCIPAL ,a.GS_PAY_STATUS  ");
            tSql.append(" from gs_pay_receive_able a,gs_provider b where 1=1 and a.gs_workcorp_id=b.gs_provider_id  ");
            if(vo.provname!=null && !"".equals(vo.provname)){
                tSql.append(" and b.gs_provider_shortname = '"+vo.provname+"' ");
            }
            tSql.append(" ) ut");
            break;
        case 0:
            tSql.append("select ut.deptname as P1,ut.gs_workcorp_category as P2,ut.GS_FEE_TYPE_ID as P3,");
            tSql.append("ut.gs_pay_type as P4,ut.GS_BALANCE_NEED as P5,ut.GS_BALANCE_DONE as P6,");
            tSql.append("ut.GS_BALANCE_UNDONE as P7,ut.GS_PAY_DATE as P8,ut.GS_PRINCIPAL as P9,");
            tSql.append("ut.GS_PAY_STATUS as P10,bc.gs_fee_type as P11 ");
            tSql.append("from(");

            tSql.append("select b.gs_org_name as deptname,a.gs_workcorp_category ,a.GS_FEE_TYPE_ID ,");
            tSql.append("a.gs_pay_type ,a.GS_BALANCE_NEED ,a.GS_BALANCE_DONE ,");
            tSql.append("a.GS_BALANCE_UNDONE ,a.GS_PAY_DATE ,a.GS_PRINCIPAL ,a.GS_PAY_STATUS ");
            tSql.append(" from gs_pay_receive_able a,gs_organization b where 1=1 and a.gs_workcorp_id = b.gs_org_id  ");
            if(vo.deptid != null && !"".equals(vo.deptid)){
                tSql.append(" and a.gs_workcorp_id = '"+vo.deptid+"' ");    
            } 
            tSql.append(" union ");

            tSql.append("select b.gs_provider_shortname as deptname,a.gs_workcorp_category ,a.GS_FEE_TYPE_ID ,");
            tSql.append("a.gs_pay_type ,a.GS_BALANCE_NEED ,a.GS_BALANCE_DONE ,");
            tSql.append("a.GS_BALANCE_UNDONE ,a.GS_PAY_DATE ,a.GS_PRINCIPAL ,a.GS_PAY_STATUS  ");
            tSql.append(" from gs_pay_receive_able a,gs_provider b where 1=1 and a.gs_workcorp_id=b.gs_provider_id  ");
            if(vo.provname!=null && !"".equals(vo.provname)){
                tSql.append(" and b.gs_provider_shortname = '"+vo.provname+"' ");
            }
            tSql.append(") ut");
            break;
        }
        tSql.append(" left join gs_balance_fee bc on ut.gs_fee_type_id = bc.gs_fee_type_no where 1=1 ");

        if(vo.alertdate != null && !"".equals(vo.alertdate)){
            tSql.append(" and to_char(SYSDATE+" + vo.alertdate + ",'YYYY-MM-DD') >=ut.GS_PAY_DATE " );
        }
        int paytype = Integer.parseInt(vo.paytype);
        switch(paytype){
        case 1:
            tSql.append(" and ut.GS_PAY_TYPE = 1 ");
            break;
        case 2:
            tSql.append(" and ut.GS_PAY_TYPE = 2 ");
            break;
        case 0:break;
        }
        int paystatus = Integer.parseInt(vo.paystatus);
        switch(paystatus){
        case 1:tSql.append(" and GS_PAY_STATUS = 1 ");break;
        case 2:tSql.append(" and GS_PAY_STATUS = 2 ");break;
        case 3:tSql.append(" and GS_PAY_STATUS = 3 ");break;
        case 0:break;
        }
        //sql构造完成
        return tSql;

    }
    
    public ArrayList getPayReceiveStatusList(GSOperatorBean operatorBean, GSPayReceiveStatusQueryVO vo,int pageNo,int pageSize) throws GSException {
        ArrayList ary = new ArrayList();
        StringBuffer tSql = getStatusSql(operatorBean,vo);
        try{
            GSPayReceiveAbleDAO prd = new GSPayReceiveAbleDAO();
            
            ary = prd.executeQuery(new GSPayReceiveMentStatusListVO(),tSql.toString(),null,pageNo,pageSize);
        }catch(Exception e){
            throw new GSException (GSException.E20080,GSException.E20080_MSG);
        }
        
        return ary;        
    }
    
    public int getPayReceiveStatusListCount(GSOperatorBean operatorBean, GSPayReceiveStatusQueryVO vo) throws GSException {
        int count = 0;
        StringBuffer tSqlBuffer = getStatusSql(operatorBean,vo);
        String tSql = "select count(*) from(" + tSqlBuffer.toString() + ")";
        try{
            GSPayReceiveAbleDAO prd = new GSPayReceiveAbleDAO();
            
            count = prd.queryRowNum(tSql,null);
        }catch(Exception e){
            throw new GSException (GSException.E20080,GSException.E20080_MSG);
        }
        
        return count;        
    }
    
    public StringBuffer getStatusSql(GSOperatorBean operatorBean, GSPayReceiveStatusQueryVO vo){
        StringBuffer tSql = new StringBuffer("");

        int workcorpcategory = Integer.parseInt(vo.workcorpcategory);
        switch(workcorpcategory){
        case 1:
            tSql.append("select ut.gs_pay_workcorp_id as P1,ut.deptname as P2,ut.gs_pay_workcorp_category as P3,");
            tSql.append("ut.gs_pay_category as P4,ut.gs_actual_pay_date as P5,ut.gs_actual_pay_num as P6,");
            tSql.append("ut.GS_FUNCTIONARY as P7 ");
            tSql.append("from(");
            
            tSql.append("select a.gs_pay_workcorp_id,b.gs_org_name as deptname,a.gs_pay_workcorp_category ,");
            tSql.append("a.gs_pay_category ,a.gs_actual_pay_date ,a.gs_actual_pay_num ,");
            tSql.append("a.GS_FUNCTIONARY  ");
            tSql.append(" from gs_pay_receive_ment a,gs_organization b where 1=1 and a.gs_pay_workcorp_id = b.gs_org_id ");
            if(vo.deptid != null && !"".equals(vo.deptid)){
                tSql.append(" and a.gs_pay_workcorp_id = '"+vo.deptid+"' ");    
            }            
            tSql.append(" ) ut");
            break;
        case 2:
            tSql.append("select ut.gs_pay_workcorp_id as P1,ut.deptname as P2,ut.gs_pay_workcorp_category as P3,");
            tSql.append("ut.gs_pay_category as P4,ut.gs_actual_pay_date as P5,ut.gs_actual_pay_num as P6,");
            tSql.append("ut.GS_FUNCTIONARY as P7 ");
            tSql.append("from(");
            
            tSql.append("select a.gs_pay_workcorp_id,b.gs_provider_shortname as deptname,a.gs_pay_workcorp_category ,");
            tSql.append("a.gs_pay_category ,a.gs_actual_pay_date ,a.gs_actual_pay_num ,");
            tSql.append("a.GS_FUNCTIONARY  ");
            tSql.append(" from gs_pay_receive_ment a,gs_provider b where 1=1 and a.gs_pay_workcorp_id=b.gs_provider_id  ");
            if(vo.provname!=null && !"".equals(vo.provname)){
                tSql.append(" and b.gs_provider_shortname = '"+vo.provname+"' ");
            }
            tSql.append(" ) ut");
            break;
        case 0:
            tSql.append("select ut.gs_pay_workcorp_id as P1,ut.deptname as P2,ut.gs_pay_workcorp_category as P3,");
            tSql.append("ut.gs_pay_category as P4,ut.gs_actual_pay_date as P5,ut.gs_actual_pay_num as P6,");
            tSql.append("ut.GS_FUNCTIONARY as P7 ");
            tSql.append("from(");

            tSql.append("select a.gs_pay_workcorp_id,b.gs_org_name as deptname,a.gs_pay_workcorp_category ,");
            tSql.append("a.gs_pay_category ,a.gs_actual_pay_date ,a.gs_actual_pay_num ,");
            tSql.append("a.GS_FUNCTIONARY  ");
            tSql.append(" from gs_pay_receive_ment a,gs_organization b where 1=1 and a.gs_pay_workcorp_id = b.gs_org_id  ");
            if(vo.deptid != null && !"".equals(vo.deptid)){
                tSql.append(" and a.gs_pay_workcorp_id = '"+vo.deptid+"' ");    
            } 
            tSql.append(" union ");

            tSql.append("select a.gs_pay_workcorp_id,b.gs_provider_shortname as deptname,a.gs_pay_workcorp_category ,");
            tSql.append("a.gs_pay_category ,a.gs_actual_pay_date ,a.gs_actual_pay_num ,");
            tSql.append("a.GS_FUNCTIONARY  ");
            tSql.append(" from gs_pay_receive_ment a,gs_provider b where 1=1 and a.gs_pay_workcorp_id=b.gs_provider_id  ");
            if(vo.provname!=null && !"".equals(vo.provname)){
                tSql.append(" and b.gs_provider_shortname = '"+vo.provname+"' ");
            }
            tSql.append(") ut ");
            break;
        }
        
        tSql.append(" where 1=1 ");
        int paytype = Integer.parseInt(vo.paytype);
        switch(paytype){
        case 1:
            tSql.append(" and ut.GS_PAY_CATEGORY = 1 ");
            break;
        case 2:
            tSql.append(" and ut.GS_PAY_CATEGORY = 2 ");
            break;
        case 0:break;
        }
        if(vo.payreceivedatestart != null && !"".equals(vo.payreceivedatestart)){
            tSql.append(" and ut.gs_actual_pay_date >= '" + vo.payreceivedatestart + "'");            
        }
        if(vo.payreceivedateend != null && !"".equals(vo.payreceivedateend)){
            tSql.append(" and ut.gs_actual_pay_date <= '" + vo.payreceivedateend + "'");            
        }
        
        
        

        return tSql;
    }
    
    public GSPayReceiveMentBean getPayReceiveMentBean(String workcorpId ,String payDate) throws GSException {
        GSPayReceiveAbleDAO dao = new GSPayReceiveAbleDAO();
        GSPayReceiveMentBean bean = null;
        try{
            String tSql = "select * from gs_pay_receive_ment t where t.gs_pay_workcorp_id=? and t.gs_actual_pay_date=?";
            Object[] obj = new Object[]{workcorpId,payDate};
            ArrayList ary = dao.executeQuery(new GSPayReceiveMentBean(),tSql,obj);
            if(ary != null && ary.size() != 0)
                bean = (GSPayReceiveMentBean)ary.get(0);
        }catch(Exception e){
            throw new GSException(GSException.E20085,GSException.E20085_MSG);
        }
        
        return bean;
    }
    
    public ArrayList getPayReceiveAbleListByPayReceiveMent(GSPayReceiveMentBean tBean) throws GSException {
        String workcorpId = tBean.getGs_pay_workcorp_id();
        String payTime = tBean.getGs_invoice_pay_date();
        String invoiceNo = tBean.getGs_invoice_code()==null?"":tBean.getGs_invoice_code().trim();
        String category = tBean.getGs_pay_workcorp_category();
        
        GSPayReceiveAbleDAO dao = new GSPayReceiveAbleDAO();
        ArrayList ary = null;
        try{
            StringBuffer tSql = new StringBuffer("");
            if("1".equals(category)){
                tSql.append("select ut.gs_workcorp_id as P1,ut.deptname as P2,c.gs_fee_type as P3, ");
                tSql.append("ut.gs_balance_need as P4,ut.GS_OFFSET_DATE as P5,ut.GS_PRINCIPAL as P6, ");
                tSql.append("ut.gs_pay_status as P7,ut.gs_invoice_code as P8,ut.gs_fee_type_id as P9 from ");
                tSql.append("(select a.*,b.gs_org_name as deptname from gs_pay_receive_able a,gs_organization b ");
                tSql.append("where a.gs_workcorp_id = b.gs_org_id) ut ");
                tSql.append("left join gs_balance_fee c on ut.gs_fee_type_id = c.gs_fee_type_no ");
                tSql.append("where ut.gs_workcorp_id=? ");
                
            }else{
                tSql.append("select ut.gs_workcorp_id as P1,ut.deptname as P2,c.gs_fee_type as P3, ");
                tSql.append("ut.gs_balance_need as P4,ut.GS_OFFSET_DATE as P5,ut.GS_PRINCIPAL as P6, ");
                tSql.append("ut.gs_pay_status as P7,ut.gs_invoice_code as P8,ut.gs_fee_type_id as P9 from ");
                tSql.append("(select a.*,b.gs_provider_shortname as deptname from gs_pay_receive_able a,gs_provider b ");
                tSql.append("where a.gs_workcorp_id = b.gs_provider_id) ut ");
                tSql.append("left join gs_balance_fee c on ut.gs_fee_type_id = c.gs_fee_type_no ");
                tSql.append("where ut.gs_workcorp_id=? ");
                
            }
            if(invoiceNo !=null && !"".equals(invoiceNo)){
                tSql.append(" and ut.gs_invoice_code like ?");
            }
            
            Object[] obj = new Object[]{workcorpId,"%"+invoiceNo+"%"};
            ary = dao.executeQuery(new GSPayReceiveAbleStatusListVO(),tSql.toString(),obj);
        }catch(Exception e){
            throw new GSException(GSException.E20085,GSException.E20085_MSG);
        }
        
        return ary;        
    }
    /**
     * 查询应收应付表中未完成或部分完成信息
     * @param aVO               查询条件
     * @param pageNo            页号（用于分页）
     * @param pageSize          页笔数（用于分页）
     * @param aOrg              订货单位
     * @return
     * @throws GSException
     */
    public ArrayList getUnCompletedPayRecvInfo(GSBalanceManageQueryVO aVO,int pageNo,int pageSize,ArrayList aOrg) throws GSException{
        ArrayList tList = new ArrayList();
        GSPayReceiveAbleDAO tPayRecvAbleDAO = new GSPayReceiveAbleDAO();
        GSBalanceFeeDAO tBalFeeDAO = new GSBalanceFeeDAO();
        ArrayList tRs = tPayRecvAbleDAO.queryUnCompletedPayRecvInfo(aVO,pageNo,pageSize,aOrg);
        if(tRs.size() > 0){
            for(int i=0; i< tRs.size(); i++){
                String[] tStr = new String[14];
                GSBalPayRecvManageQryResultVO tVO = (GSBalPayRecvManageQryResultVO)tRs.get(i);
                tStr[0] = tVO.balanceYM;
                tStr[1] = tVO.deptId;
                tStr[2] = tVO.deptName;
                tStr[3] = tVO.deptCategory;
                tStr[4] = tVO.feeTypeNo;
                tStr[5] = tVO.payType;
                tStr[6] = StringUtils.formatEDouble(tVO.balanceNeed,4);
                tStr[7] = StringUtils.formatEDouble(tVO.balanceDone,4);
                tStr[8] = StringUtils.formatEDouble(tVO.balanceUndone,4);
                tStr[9] = tVO.payDate;
                tStr[10] = tVO.principal;
                tStr[11] = tVO.status;
                tStr[12] = tBalFeeDAO.queryFeeType(tStr[4]);
                tStr[13] = tVO.receiveableId;
                tList.add(tStr);
            }
        }
        return tList;
    }
    /**
     * 修改应收应付表中的收付日期和负责人
     * @param aVO               待修改数据
     * @param aOperId           操作员编码
     * @return
     * @throws GSException
     */
    public boolean updatePayRecvInfo(GSBalPayRecvManageVO aVO,String aOperId) throws GSException{
        boolean tFlag = false;
        GSPayReceiveAbleDAO tPayReceiveAbleDAO = new GSPayReceiveAbleDAO();
        tFlag = tPayReceiveAbleDAO.updatePayRecvAbleInfo(aVO,aOperId);
        return tFlag;
    }
    /**
     * 新增应收应付信息
     * @param aVO           新增数据封装对象
     * @param aOperId       操作员编码
     * @return
     * @throws GSException
     */
    public boolean addPayRecvInfo(GSBalPayRecvAddVO aVO,String aOperId) throws GSException{
        boolean tFlag = false;
        GSPayReceiveAbleDAO tPayRecvDAO = new GSPayReceiveAbleDAO();
        GSPayReceiveAbleBean tBean = new GSPayReceiveAbleBean();
        Connection tConn = null;
        try{
            
            tBean.setGs_workcorp_category(aVO.deptCategory);
            //判断单位类型
            if(aVO.deptCategory.equals(GSBaseConstants.BALANCE_ORDERDEPT_TYPE)){
                //设定订货单位编码
                tBean.setGs_workcorp_id(aVO.deptId);
                //判断是否是运营公司级
                if(aVO.deptId.substring(2,4).equals("00")){
                    tBean.setGs_workcorp_level("1");
                }else{
                    tBean.setGs_workcorp_level("2");
                }
            }else{
                //根据供应商简称查询供应商
                GSProviderDAO tProvderDAO = new GSProviderDAO();
                GSProviderBean tProvBean = tProvderDAO.queryProviderByShortName(aVO.provider);
                if(tProvBean == null){
                    GSLogWriter.error(this,GSException.E40009_MSG);
                    throw new GSException(GSException.E40009,GSException.E40009_MSG);
                }
                //设定供应商编码
                tBean.setGs_workcorp_id(tProvBean.getGs_provider_id());
                //设定默认值1
                tBean.setGs_workcorp_level("1");
            }
            tBean.setGs_balance_month(aVO.balanceYM);
            tBean.setGs_balance_need(aVO.sum);
            tBean.setGs_balance_done(0);
            tBean.setGs_balance_undone(aVO.sum);
            tBean.setGs_fee_type_id(aVO.feeTypeNo);
            tBean.setGs_pay_type(aVO.payType);
            tBean.setGs_pay_date(aVO.payDate);
            tBean.setGs_principal(aVO.principal);
            tBean.setGs_pay_status(GSBaseConstants.UNPAY);
            tBean.setGs_notes(aVO.notes);
            tBean.setGs_operator_id(aOperId);
            String tCurDT = DateUtil.getCurrentDate("yyyy-MM-dd HH:mm:ss");
            tBean.setGs_operator_date(tCurDT);
            String tCurT = DateUtil.getCurrentDate("MM-dd hh:mm:ss");
            tBean.setGs_offset_date("1970"+"-"+ tCurT);
            tBean.setGs_receiveable_id(GenRandNum.genDTRandNum());      //生成随即数作为应收应付表的关键字
            tConn = tPayRecvDAO.getConnetion();
            //插入DB
            tFlag = tPayRecvDAO.insertPayReceiveInfo(tBean,tConn);
        }catch(GSException ge){
            throw ge;
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(e.toString());
        }finally{
            try{
                if(tConn != null){
                    tConn.close();
                    tConn = null;
                }
            }catch(SQLException se){
                se.printStackTrace();
            }
        }
        return tFlag;
    }
    /**
     * 取得指定时间段内的应收应付金额
     * @param aMonth            月份
     * @return
     * @throws GSException
     */
    public ArrayList getCurMonthPayRecvInfo(String aBegin,String aEnd) throws GSException{
        ArrayList tList = new ArrayList();
        double tPayAmt = 0;
        double tRecAmt = 0;
        GSPayReceiveAbleDAO tPayRecvDAO = new GSPayReceiveAbleDAO();
        //查询当月应付信息
        ArrayList tPayList = tPayRecvDAO.queryCurMonthPayRecvInfo(aBegin,aEnd,GSBaseConstants.PAYMENT_TYPE);
        if(tPayList.size() > 0){
            for(int i=0; i<tPayList.size(); i++){
                GSPayReceiveAbleBean tPayReceiveAbleBean = (GSPayReceiveAbleBean)tPayList.get(i);
                //累加应付金额
                tPayAmt = tPayAmt + tPayReceiveAbleBean.getGs_balance_undone();
            }
        }
        //查询当月应收信息
        ArrayList tRecvList = tPayRecvDAO.queryCurMonthPayRecvInfo(aBegin,aEnd,GSBaseConstants.RECEIVE_TYPE);
        if(tRecvList.size() > 0){
            for(int i=0; i<tRecvList.size(); i++){
                GSPayReceiveAbleBean tPayReceiveAbleBean = (GSPayReceiveAbleBean)tRecvList.get(i);
                //累计应收金额
                tRecAmt = tRecAmt + tPayReceiveAbleBean.getGs_balance_undone();
            }
        }
        tList.add(0,StringUtils.formatEDouble(tPayAmt,4));
        tList.add(1,StringUtils.formatEDouble(tRecAmt,4));
        return tList;
    }
}


